package com.ruc.deke.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.springframework.stereotype.Component;

import com.ruc.deke.dao.ExamDAO;
import com.ruc.deke.model.DBRecord;
import com.ruc.deke.utils.ParseUtil;
//import jdk.nashorn.internal.runtime.arrays.ArrayData;

@Component(value = "examDAOImpl")
public class ExamDAOImpl implements ExamDAO {
	@Resource(name = "dataSource_rucdbcourse")
	private DataSource dataSource;

	@Override
	public ArrayList<DBRecord> getExamsByModuleId(int moduleId,String ano) {
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		ArrayList<DBRecord> records = new ArrayList<DBRecord>();

		try {
			String sql = "select * from exp_module where moduleid = " + moduleId + " and (ano='"+ano+"' or ano='0000') order by expid";
			connection = dataSource.getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			records = ParseUtil.toDBRecord(resultSet);
			statement.close();
			connection.close();
		} catch (SQLException e) {
			System.out.println("exception:" + e.getMessage());
		}

		return records;
	}

	@Override
	public ArrayList<DBRecord> getQuestionsByPaperId(String paperId) {
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		ArrayList<DBRecord> records = new ArrayList<DBRecord>();

		try {
			// String sql="select questionid from paper_exp where paperid = " +
			// paperId + "order by questionid";

			String sql = "select * from exp_module where questionid in "
					+ "(select questionid from paper_exp where paperid = '" + paperId + "'order by questionid)";
			System.out.println(sql);
			connection = dataSource.getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			records = ParseUtil.toDBRecord(resultSet);
			statement.close();
			connection.close();
		} catch (SQLException e) {
			System.out.println("error in  getQuestionsByPaperId");
			System.out.println("exception:" + e.getMessage());
		}

		return records;
	}

	@Override
	public ArrayList<DBRecord> getPaperListByTeacherId(int moduleid, String ano) {
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		ArrayList<DBRecord> records = new ArrayList<DBRecord>();

		try {
			// System.out.println("teacherID: "+teacherId);
			String sql = "select * from paper_info where ano = '" + ano + "' and pmoduleid = " + moduleid;
			System.out.println(sql);
			connection = dataSource.getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			records = ParseUtil.toDBRecord(resultSet);
			statement.close();
			connection.close();
		} catch (SQLException e) {
			System.out.println("exception:" + e.getMessage());
		}

		return records;
	}

	public ArrayList<DBRecord> getExplistByAnoAndModuleId(int moduleId, String ano) {
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		ArrayList<DBRecord> explist = new ArrayList<DBRecord>();

		try {
			// System.out.println("teacherID: "+teacherId);
			// gyh note:change!!
			String sql = "select * from exp_module where ANO = '" + ano + "' and MODULEID = " + moduleId;

			System.out.println("&&&&&&&&&&&&&&&&&&&&&&&&&&&&&" + sql);
			connection = dataSource.getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			explist = ParseUtil.toDBRecord(resultSet);
			statement.close();
			connection.close();
		} catch (SQLException e) {
			System.out.println("exception:" + e.getMessage());
		}

		return explist;
	}
	//通过expid和moduleid获取问题信息
		public ArrayList<DBRecord> getExpByQuestionIdAndModuleId(int questionId,int moduleId) {
			Connection connection = null;
			Statement statement = null;
			ResultSet resultSet = null;
			ArrayList<DBRecord> explist2 = new ArrayList<DBRecord>();

			try {
				String sql = "select * from exp_module where QUESTIONID = '" + questionId + "' and MODULEID = " + moduleId;

				System.out.println("&&&&&&&&&&&&&&&&&&&&&&&&&&&&&" + sql);
				connection = dataSource.getConnection();
				statement = connection.createStatement();
				resultSet = statement.executeQuery(sql);
				explist2 = ParseUtil.toDBRecord(resultSet);
				statement.close();
				connection.close();
			} catch (SQLException e) {
				System.out.println("exception:" + e.getMessage());
			}

			return explist2;
		}
		//end
	public String insertPaperExp(Connection connection, DBRecord record) {
		int result = 0;
		String info = "success";
		Statement statement = null;
		try {
			String sql = ParseUtil.toInsertSQL("PAPER_EXP", record);
			statement = connection.createStatement();
			result = statement.executeUpdate(sql);
		} catch (SQLException e) {
			info = "failed";
			// e.printStackTrace();
			System.out.println(e.getMessage());
		} catch (Exception e) {
			System.out.println(e.getMessage());
			info = "failed";
			// e.printStackTrace();
		} finally {
			try {
				statement.close();
			} catch (SQLException e) {
				info = "failed";
				System.out.println(e.getMessage());
				// e.printStackTrace();
			}
		}
		return info;
	}

	@Override
	public String insertPaperInfo(Connection connection, DBRecord record) {
		int result = 0;
		String info = "success";
		Statement statement = null;
		try {
			String sql = ParseUtil.toInsertSQL("PAPER_INFO", record);
			System.err.println(sql);
			statement = connection.createStatement();
			result = statement.executeUpdate(sql);
		} catch (SQLException e) {
			info = "failed";
			// e.printStackTrace();
			System.out.println(e.getMessage());
		} catch (Exception e) {
			System.out.println(e.getMessage());
			info = "failed";
			// e.printStackTrace();
		} finally {
			try {
				statement.close();
			} catch (SQLException e) {
				info = "failed";
				System.out.println(e.getMessage());
				// e.printStackTrace();
			}
		}
		return info;
	}

	@Override
	/**
	 * @author gyh_f 待test！
	 * 
	 */
	// Boolean b = examDAO.insertNewExp(moduleId,edesc,solution,extra,ano);
	public Boolean insertNewExp(int moduleId, String edesc, String solution, String extra, String ano, int befmoduleId,
			int befexpid,int sceneId) {
		ResultSet resultSet = null;
		Statement statement = null;
		Connection connection = null;
		String sql = "select * from EXP_MODULE where MODULEID = " + moduleId;
		DBRecord record = new DBRecord();
		int rowCount = 0;
		int expId = 0;
		int jdbc_result = 0;
		ArrayList<DBRecord> templist = new ArrayList<DBRecord>();

		// 建立conn
		try {
			connection = dataSource.getConnection();
			statement = connection.createStatement();
		} catch (SQLException e) {
			System.out.println("exception:" + e.getMessage());
			return false;
		}

		// 第一次连接数据库：获得一个正确的expid
		// try..catch
		try {
			resultSet = statement.executeQuery(sql);
			templist = ParseUtil.toDBRecord(resultSet);
			rowCount = templist.size();
			expId = rowCount + 1;
		} catch (SQLException e) {
			System.out.println("herehere:exception:" + e.getMessage());
			return false;
		}

		// 做一个record 利用parseUtil得到插入的sql语句
		record.setField("EXPID", "" + expId);
		record.setField("MODULEID", "" + moduleId);
		record.setField("EDESC", "" + edesc);
		
		for(int i=0;i<solution.length();i++) {
			if(solution.charAt(i)=='\'') {
				String str1=solution.substring(0, i);
				String str2=solution.substring(i, solution.length());
				solution=str1+"'"+str2;
				i=i+2;
			}
			System.out.println(solution);
		}
		
		record.setField("SOLUTION", "" + solution);
		
		for(int i=0;i<extra.length();i++) {
			if(extra.charAt(i)=='\'') {
				String str1=extra.substring(0, i);
				String str2=extra.substring(i, extra.length());
				extra=str1+"'"+str2;
				i=i+2;
			}
			System.out.println(extra);
		}
		
		record.setField("EXTRA", "" + extra);
		// record.setField("QUESTIONID", "" + );//自动补齐
		record.setField("SCENEID", "" +sceneId);
		record.setField("ANO", "" + ano);
		record.setField("BEFOREMODULEID",""+ befmoduleId);
		record.setField("BEFOREEXPID",""+befexpid);
		// 第二次连接数据库：executeUpdate
		try {
			String sql2 = ParseUtil.toInsertSQL("EXP_MODULE", record);
			System.out.println("_______________________________________________");
			System.out.println(sql2);
			System.out.println(sceneId);
			System.out.println("_______________________________________________");
			// 重新定义句柄
			statement = connection.createStatement();
			jdbc_result = statement.executeUpdate(sql2);

		} catch (SQLException e) {
			System.out.println("L213");
			System.out.println(e.getMessage());
		} catch (Exception e) {
			System.out.println("L216");
			System.out.println(e.getMessage());
		} finally {
			try {
				statement.close();
			} catch (SQLException e) {
				System.out.println("L222");
				System.out.println(e.getMessage());
				return false;
			}

		}

		return true;
	}
	    //修改题目信息
		public Boolean changeExp(String edesc,String solution,String type,int questionId) {
		ResultSet resultSet = null;
		Statement statement = null;
		Connection connection = null;
		String sql = "select * from EXP_MODULE where QUESTIONID="+questionId;
		DBRecord record = new DBRecord();
		int rowCount = 0;
		int expId = 0;
		int jdbc_result = 0;
		ArrayList<DBRecord> templist = new ArrayList<DBRecord>();

		// 建立conn
		try {
			connection = dataSource.getConnection();
			statement = connection.createStatement();
		} catch (SQLException e) {
			System.out.println("exception:" + e.getMessage());
			return false;
		}

		// 第一次连接数据库：获得一个正确的expid
		// try..catch
		try {
			resultSet = statement.executeQuery(sql);
			templist = ParseUtil.toDBRecord(resultSet);
			rowCount = templist.size();
			expId = rowCount + 1;
		} catch (SQLException e) {
			System.out.println("herehere:exception:" + e.getMessage());
			return false;
		}

		// 做一个record 利用parseUtil得到插入的sql语句
		//record.setField("EXPID", "" + expId);
		DBRecord record1 = new DBRecord();
		DBRecord record2 = new DBRecord();
		record1.setField("SOLUTION", "" + solution);
		record1.setField("EDESC", "" + edesc);
		record1.setField("EXTRA", "" + type);
		record2.setField("QUESTIONID", "" + questionId);

		try {
			String sql2 = ParseUtil.toUpdataSQL("EXP_MODULE", record1,record2);
			System.out.println("######################");
			System.out.println(sql2);
			statement = connection.createStatement();
			jdbc_result = statement.executeUpdate(sql2);

		} catch (SQLException e) {
			System.out.println("L213");
			System.out.println(e.getMessage());
		} catch (Exception e) {
			System.out.println("L216");
			System.out.println(e.getMessage());
		} finally {
			try {
				statement.close();
			} catch (SQLException e) {
				System.out.println("L222");
				System.out.println(e.getMessage());
				return false;
			}

		}

		return true;
	}
	//end

	@Override
	public String updatePaperInfo(Connection conn, DBRecord newRecord, DBRecord conditions) throws SQLException {
		Statement stmt = null;
		String result = "success";
		String sql = "";
		try {
			sql = ParseUtil.toUpdataSQL("PAPER_INFO", newRecord, conditions);
		} catch (Exception e) {
			System.out.println("ParseUtil.toUpdateSQL Exception:" + e.getMessage());
		}
		stmt = conn.createStatement();
		stmt.executeUpdate(sql);
		stmt.close();
		return result;
	}

	@Override
	public ArrayList<DBRecord> getScene() {
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		ArrayList<DBRecord> records = new ArrayList<DBRecord>();

		try {
			String sql = "select * from exp_scene order by sceneid";
			connection = dataSource.getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			records = ParseUtil.toDBRecord(resultSet);
			statement.close();
			connection.close();
		} catch (SQLException e) {
			// e.printStackTrace();
			System.out.println("exception:" + e.getMessage());
		}

		return records;
	}
	
	//删除试卷
	@Override
	public Boolean delPaper(String paperId,String teacherId) {
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		int resul;
		try {
			String sql = "delete from PAPER_INFO where PAPERID='"+paperId+"' and TEACHERID='"+teacherId+"'";
			System.out.println(sql);
			connection = dataSource.getConnection();
			statement = connection.createStatement();
			statement.executeUpdate(sql);
			statement.close();
			connection.close();
		}catch (SQLException e) {
			// e.printStackTrace();
			System.out.println("exception:" + e.getMessage());
			return false;
		}
		return true;
	}
	
	@Override
	public ArrayList<DBRecord> getscene() {
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		ArrayList<DBRecord> records = new ArrayList<DBRecord>();
		try {
			String sql = "select * from EXP_SCENE";
			connection = dataSource.getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			records = ParseUtil.toDBRecord(resultSet);
			statement.close();
			connection.close();
		}catch (SQLException e) {
			// e.printStackTrace();
			System.out.println("exception:" + e.getMessage());
		}
		System.out.println("EXP_SCENE load success");
		return records;
	}
	
	@Override
	public Boolean delExps(int questionId,int moduleId) {
		boolean t;
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			String sql = "delete from EXP_MODULE where QUESTIONID="+questionId;
			System.out.println(sql);
			connection = dataSource.getConnection();
			statement = connection.createStatement();
			statement.executeUpdate(sql);
			statement.close();
			connection.close();
		}catch (SQLException e) {
			// e.printStackTrace();
			System.out.println("exception:" + e.getMessage());
			return false;
		}
		return true;
	}

}
